<?php

/**
 * This is the model class for table "ecom_transaction".
 *
 * The followings are the available columns in table 'ecom_transaction':
 * @property string $transaction_id
 * @property string $transaction_code
 * @property string $transaction_buyer_network_id
 * @property string $transaction_recipient_name
 * @property string $transaction_recipient_address
 * @property string $transaction_recipient_phone
 * @property string $transaction_total_quantity
 * @property string $transaction_total_price
 * @property string $transaction_datetime
 * @property string $transaction_is_approve
 */
class ecom_transaction extends CActiveRecord {
    
    public $transaction_date;
    public $transaction_start_date;
    public $transaction_end_date;
    public $products_code;
    public $products_name;
    public $member_mid;
    public $periode;
    public $omset;
    public $bonus;

    /**
     * Returns the static model of the specified AR class.
     * @return ecom_transaction the static model class
     */
    public static function model($className=__CLASS__) {
        return parent::model($className);
    }

    /**
     * @return string the associated database table name
     */
    public function tableName() {
        return 'ecom_transaction';
    }

    /**
     * @return array validation rules for model attributes.
     */
    public function rules() {
        // NOTE: you should only define rules for those attributes that
        // will receive user inputs.
        return array(
            array('transaction_code, transaction_recipient_name, transaction_recipient_phone', 'length', 'max' => 50),
            array('transaction_buyer_network_id', 'length', 'max' => 20),
            array('transaction_total_quantity, transaction_total_price', 'length', 'max' => 10),
            array('transaction_is_approve', 'length', 'max' => 1),
            array('transaction_recipient_address, transaction_datetime', 'safe'),
            // The following rule is used by search().
            // Please remove those attributes that should not be searched.
            array('member_mid,transaction_id, transaction_code, transaction_buyer_network_id, transaction_recipient_name, transaction_recipient_address, transaction_recipient_phone, transaction_total_quantity, transaction_total_price, transaction_datetime, transaction_is_approve', 'safe', 'on' => 'search'),
            array('transaction_id, transaction_code, products_code, products_name, transaction_date, transaction_buyer_network_id, transaction_recipient_name, transaction_recipient_address, transaction_recipient_phone, transaction_total_quantity, transaction_total_price, transaction_datetime, transaction_is_approve', 'safe', 'on' => 'search_sales'),
            array('omset,periode,bonus', 'safe', 'on' => 'search_monthly'),
            );
    }

    /**
     * @return array relational rules.
     */
    public function relations() {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'rel_transaction_to_network' => array(self::BELONGS_TO, 'sys_network', 'transaction_buyer_network_id'),
            'rel_transaction_to_transaction_detail' => array(self::HAS_MANY, 'ecom_transaction_detail', 'transaction_detail_transaction_id'),
        );
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels() {
        return array(
            'transaction_id' => 'Transaction',
            'transaction_code' => 'Transaction Code',
            'transaction_buyer_network_id' => 'Buyer',
            'transaction_recipient_name' => 'Recipient Name',
            'transaction_recipient_address' => 'Recipient Address',
            'transaction_recipient_phone' => 'Recipient Phone',
            'transaction_total_quantity' => 'Total Quantity',
            'transaction_total_price' => 'Total Price',
            'transaction_datetime' => 'Transaction Datetime',
            'transaction_is_approve' => 'Transaction Is Approve',
            'transaction_start_date' => 'Start Date',
            'transaction_end_date' => 'End Date',
            'products_code' => 'Products Code',
            'products_name' => 'Products Name',
        );
    }

    /**
     * Retrieves a list of models based on the current search/filter conditions.
     * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
     */
    public function search() {
        // Warning: Please modify the following code to remove attributes that
        // should not be searched.

        $criteria = new CDbCriteria;
        
        $criteria->join = "LEFT JOIN sys_network n 
            ON t.transaction_buyer_network_id = n.network_id";
        
        $criteria->order="transaction_datetime DESC";
        $criteria->compare('transaction_id', $this->transaction_id, true);
        $criteria->compare('network_mid', $this->member_mid, true);
        $criteria->compare('transaction_code', $this->transaction_code, true);
        $criteria->compare('transaction_buyer_network_id', $this->transaction_buyer_network_id, true);
        $criteria->compare('transaction_recipient_name', $this->transaction_recipient_name, true);
        $criteria->compare('transaction_recipient_address', $this->transaction_recipient_address, true);
        $criteria->compare('transaction_recipient_phone', $this->transaction_recipient_phone, true);
        $criteria->compare('transaction_total_quantity', $this->transaction_total_quantity, true);
        $criteria->compare('transaction_total_price', $this->transaction_total_price, true);
        $criteria->compare('transaction_datetime', $this->transaction_datetime, true);
        $criteria->compare('transaction_is_approve', $this->transaction_is_approve, true);

        return new CActiveDataProvider($this, array(
                    'criteria' => $criteria,
                ));
    }
    
    /**
     * Retrieves a list of models based on the current search/filter conditions.
     * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
     */
    public function search_sales() {
        // Warning: Please modify the following code to remove attributes that
        // should not be searched.
        $this->transaction_start_date = '';

        $criteria = new CDbCriteria;
        
        $criteria->select = "DATE(t.transaction_datetime) AS transaction_date, 
            ecom_transaction_detail.transaction_detail_products_code AS products_code, 
            ecom_transaction_detail.transaction_detail_products_name AS products_name, 
            SUM(ecom_transaction_detail.transaction_detail_quantity) AS transaction_total_quantity";
        
        $criteria->join = "LEFT JOIN ecom_transaction_detail 
            ON t.transaction_id = ecom_transaction_detail.transaction_detail_transaction_id";
        
        if(isset($this->transaction_start_date) && isset($this->transaction_end_date)) {
            $criteria->condition = "DATE(transaction_datetime) BETWEEN '".$this->transaction_start_date."' AND '".$this->transaction_end_date."'";
        }
        
        $criteria->group = "DATE(t.transaction_datetime), 
            ecom_transaction_detail.transaction_detail_products_id";

        $criteria->compare('transaction_id', $this->transaction_id, true);
        $criteria->compare('ecom_transaction_detail.transaction_detail_products_code', $this->products_code, true);
        $criteria->compare('ecom_transaction_detail.transaction_detail_products_name', $this->products_name, true);
        $criteria->compare('transaction_datetime', $this->transaction_date, true);

        return new CActiveDataProvider($this, array(
                    'criteria' => $criteria,
                ));
    }
    
    /**
     * Retrieves a list of models based on the current search/filter conditions.
     * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
     */
    public function search_monthly() {
        // Warning: Please modify the following code to remove attributes that
        // should not be searched.
        $this->transaction_start_date = '';

        $criteria = new CDbCriteria;
        
        $criteria->select = "LEFT( transaction_datetime, 7 ) AS periode, SUM( transaction_detail_price *  `transaction_detail_quantity` ) AS omset ,(SELECT sum(`gen_node_products_bonus_log_value`) FROM `sp_gen_node_products_bonus_log` where LEFT(gen_node_products_bonus_log_date,7) = periode ) as bonus ";
        
        $criteria->join = "LEFT JOIN ecom_transaction_detail  ON `transaction_detail_transaction_id` = transaction_id";
        
        $criteria->condition = "`transaction_detail_products_code` LIKE  '%GH%'";
        
        $criteria->group = "LEFT(transaction_datetime,7)";

        //$criteria->compare('transaction_id', $this->transaction_id, true);
        //$criteria->compare('ecom_transaction_detail.transaction_detail_products_code', $this->products_code, true);
        //$criteria->compare('ecom_transaction_detail.transaction_detail_products_name', $this->products_name, true);
        //$criteria->compare('transaction_datetime', $this->transaction_date, true);

        return new CActiveDataProvider($this, array(
                    'criteria' => $criteria,
                ));
    }

}